package repository;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;

import utils.SQLFactory;
import classes.Address;
import classes.City;
import classes.District;
import classes.Iterator;
import exceptions.RepositoryException;

public class AddressRepository
{
	private static AddressRepository	instance;
	private Connection					connection;
	private PreparedStatement			addStatement;
	private PreparedStatement			editStatement;
	private PreparedStatement			cityIteratorStatement;
	private PreparedStatement			containsStatement;
	private PreparedStatement			removeStatement;
	private PreparedStatement			getStatement;
	private PreparedStatement			iteratorStatement;
	private PreparedStatement			sizeStatement;

	private AddressRepository() throws RepositoryException
	{
		try
		{
			this.connection = SQLFactory.getConnectionInstance();
			this.init();
		}
		catch ( SQLException e )
		{
			throw new RepositoryException();
		}
	}

	public static AddressRepository getInstance() throws RepositoryException
	{
		if ( instance == null )
		{
			instance = new AddressRepository();
		}
		
		return instance;
	}

	private void init() throws RepositoryException
	{
		try
		{
			if ( connection != null && !connection.isClosed() )
			{
				this.addStatement = connection.prepareStatement( "insert into endereco (rua, numero, cep, bairro_id) values (?,?,?,?)" );
				this.editStatement = connection.prepareStatement( "update endereco set rua = ?, numero = ?, cep = ?, bairro_id = ? where id = ?" );
				this.containsStatement = connection.prepareStatement( "select count(*) from endereco where id = ?" );
				this.removeStatement = connection.prepareStatement( "delete from endereco where id = ?" );
				this.getStatement = connection.prepareStatement( "select * from endereco where id = ?" );
				this.iteratorStatement = connection.prepareStatement( "select * from endereco" );
				this.cityIteratorStatement = connection.prepareStatement( "select * from cidade" );
				this.sizeStatement = connection.prepareStatement( "select count(*) from endereco" );

				connection.setAutoCommit( false );
			}
		}
		catch ( Exception e )
		{
			throw new RepositoryException();
		}
	}
	
	public void add( Address address ) throws RepositoryException
	{
		try
		{
			addStatement.clearParameters();
			addStatement.setString( 1, address.getName() );
			addStatement.setString( 2, address.getNumber() );
			addStatement.setString( 3, address.getCep() );
			addStatement.setInt( 4, address.getDistrictId() );
			addStatement.execute();
			connection.commit();
		}
		catch ( SQLException e )
		{
			try
			{
				connection.rollback();
			}
			catch ( SQLException e1 ){}
			throw new RepositoryException();
		}
		
	}

	public void close() throws RepositoryException
	{
		try
		{
			if ( !connection.isClosed() )
			{
				this.getStatement.close();
				this.iteratorStatement.close();
				this.removeStatement.close();
				this.sizeStatement.close();
				this.connection.close();
			}
		}
		catch ( Exception e )
		{
			throw new RepositoryException();
		}
	}
	
	public boolean contains( int id ) throws RepositoryException
	{
		boolean ret;
		
		try
		{
			containsStatement.clearParameters();
			containsStatement.setInt( 1, id );
			ResultSet result = containsStatement.executeQuery();

			ret = false;
			if ( result.next() )
			{
				int count = result.getInt( 1 );
				ret = count == 1;
			}
			result.close();
		}
		catch ( Exception e )
		{
			throw new RepositoryException();
		}
		
		return ret;
	}
	
	public void edit( Address address ) throws RepositoryException
	{
		try
		{
			editStatement.clearParameters();
			editStatement.setString( 1, address.getName() );
			editStatement.setString( 2, address.getNumber() );
			editStatement.setString( 3, address.getCep() );
			editStatement.setInt( 4, address.getDistrictId() );
			editStatement.setInt( 5, address.getId() );
			editStatement.execute();
			connection.commit();
		}
		catch ( SQLException e )
		{
			try
			{
				connection.rollback();
			}
			catch ( SQLException e1 ){}

			throw new RepositoryException();
		}
	}

	public Iterator< Address > find( Address address ) throws RepositoryException
	{
		Iterator< Address > ret;
		
		try
		{
			String query = String.format( "select * from endereco where rua like \"%%%s%%\" and numero like \"%%%s%%\" and cep like \"%%%s%%\"", address.getName(), address.getNumber(), address.getCep() );
			if( address.getDistrictId() > 0 ) query = query + " and bairro_id = " + address.getDistrictId();
			
			Statement statement = connection.createStatement();		
			Vector< Address > vector = new Vector< Address >();
			ResultSet result = statement.executeQuery( query );
			while ( result.next() )
			{
				int id = result.getInt( "id" );
				String name = result.getString( "rua" );
				String number = result.getString( "numero" );
				String cep = result.getString( "cep" );
				int districtId = result.getInt( "bairro_id" );

				Address address2 = new Address( name, number, cep, districtId );
				address2.setId( id );
				vector.add( address2 );
			}
			result.close();
	
			ret = new Iterator< Address >( vector );
		
		}
		catch ( SQLException e )
		{
			throw new RepositoryException();
		}
		
		return ret;
	}
	
	public Iterator< District > findDistrict( District district ) throws RepositoryException
	{
		Iterator< District > ret;
		
		try
		{
			String query = String.format( "select * from bairro where nome like \"%%%s%%\"", district.getName() );
			if( district.getCityId() > 0 ) query = query + " and cidade_id = " + district.getCityId();
			
			Statement statement = connection.createStatement();		
			Vector< District > vector = new Vector< District >();
			ResultSet result = statement.executeQuery( query );
			while ( result.next() )
			{
				int id = result.getInt( "id" );
				String name = result.getString( "nome" );
				int cityId = result.getInt( "cidade_id" );

				District district2 = new District( name, cityId );
				district2.setId( id );
				vector.add( district2 );
			}
			result.close();
	
			ret = new Iterator< District >( vector );
		
		}
		catch ( SQLException e )
		{
			throw new RepositoryException();
		}
		
		return ret;
	}
	
	public int findId( Address address ) throws RepositoryException
	{
		int ret;
		
		try
		{
			String query = String.format( "select id from endereco where rua = \"%s\" and numero = \"%s\" and cep = \"%s\" and bairro_id", address.getName(), address.getNumber(), address.getCep(), address.getDistrictId() );
			
			Statement statement = connection.createStatement();
			ResultSet result = statement.executeQuery( query );
			if ( result.next() )
			{
				ret = result.getInt( "id" );
			}
			else
			{
				ret = 0;
			}
			result.close();
		}
		catch ( SQLException e )
		{
			throw new RepositoryException();
		}
		
		return ret;
	}

	public Address get( int id ) throws RepositoryException
	{
		Address ret;

		try
		{
			getStatement.clearParameters();
			getStatement.setInt( 1, id );
			ResultSet result = getStatement.executeQuery();

			ret = null;
			if ( result.next() )
			{
				String name = result.getString( "rua" );
				String number = result.getString( "numero" );
				String cep = result.getString( "cep" );
				int districtId = result.getInt( "bairro_id" );

				ret = new Address( name, number, cep, districtId );
				ret.setId( id );
			}
			result.close();
		}
		catch ( Exception e )
		{
			throw new RepositoryException();
		}

		return ret;
	}

	public Iterator< Address > getIterator() throws RepositoryException
	{
		Iterator< Address > ret;

		try
		{
			Vector< Address > vetor = new Vector< Address >();
			ResultSet result = iteratorStatement.executeQuery();
			while ( result.next() )
			{
				int id = result.getInt( "id" );
				String name = result.getString( "rua" );
				String number = result.getString( "numero" );
				String cep = result.getString( "cep" );
				int districtId = result.getInt( "bairro_id" );

				Address address2 = new Address( name, number, cep, districtId );
				address2.setId( id );
				vetor.add( address2 );
			}
			result.close();

			ret = new Iterator< Address >( vetor );
		}
		catch ( Exception e )
		{
			throw new RepositoryException();
		}

		return ret;
	}
	
	public Iterator< City > getCityIterator() throws RepositoryException
	{
		Iterator< City > ret;

		try
		{
			Vector< City > vetor = new Vector< City >();
			ResultSet result = cityIteratorStatement.executeQuery();
			while ( result.next() )
			{
				int id = result.getInt( "id" );
				String name = result.getString( "nome" );

				City city = new City( name );
				city.setId( id );
				vetor.add( city );
			}
			result.close();

			ret = new Iterator< City >( vetor );
		}
		catch ( Exception e )
		{
			throw new RepositoryException();
		}

		return ret;
	}

	public void remove( int id ) throws RepositoryException
	{
		try
		{
			removeStatement.clearParameters();
			removeStatement.setInt( 1, id );
			removeStatement.execute();
			connection.commit();
		}
		catch ( SQLException e )
		{
			try
			{
				connection.rollback();
			}
			catch ( SQLException e1 )
			{
			}

			throw new RepositoryException();
		}
	}

	public int size() throws RepositoryException
	{
		int ret;

		try
		{
			ResultSet result = sizeStatement.executeQuery();
			result.next();
			ret = result.getInt( 1 );
			result.close();
		}
		catch ( Exception e )
		{
			throw new RepositoryException();
		}

		return ret;
	}
}
